#include <sys/epoll.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/types.h>          /* See NOTES */
#include <sys/socket.h>
#include <netinet/in.h>
#include <arpa/inet.h>
#include <pthread.h>
#include <unistd.h>
#include <pthread.h>
#include <sqlite3.h>
#include <time.h>

//获取时间函数
time_t secs;
char time_buf[128] = {0};
char *Get_Time()
{
    secs = time(NULL);
    struct tm *Time = localtime(&secs);
    sprintf(time_buf,"时间:%d年%d月%d日 %d:%d:%d  ", Time->tm_year + 1900,Time->tm_mon + 1,Time->tm_mday, Time->tm_hour,Time->tm_min ,Time->tm_sec);

    return time_buf;
}

//------------信息结构体变量-------------//
#define USER_LOGIN 		0x00000000  // login	登陆    0x00000001
#define USER_MODIFY 	0x00000001  // user-modification  修改
#define USER_QUERY 		0x00000002  // user-query   查询


#define ADMIN_LOGIN 	0x10000000  // login	登陆    0x00000001
#define ADMIN_MODIFY 	0x10000001 // admin_modification
#define ADMIN_ADDUSER 	0x10000002 // admin_adduser
#define ADMIN_DELUSER 	0x10000004 // admin_deluser
#define ADMIN_QUERY 	0x10000008  //hitory_query
#define ADMIN_HISTORY 	0x10000010  //hitory_query

#define ADMIN 0	//管理员
#define USER  1	//用户

#define QUIT 			0x11111111

#define NAMELEN 512
#define DATALEN 512
#define SQL_LEN 4096
typedef struct staff_info{
    int no;              //员工编号
    int usertype;        //ADMIN(1) USER(2)
    char name[NAMELEN];  //姓名
    char passwd[8];      //密码
    int age; 		 //年龄
    char phone[NAMELEN]; //电话
    char addr[DATALEN];  //地址
    char work[DATALEN];  //职位
    char data[DATALEN];  //入职年月
    int level;           //等级
    double salary;       //工资
}staff_info_t;


/*定义双方通信结构体信息*/
typedef struct{
    int msgtype;            //请求的消息类型
    int usertype;           //ADMIN(1) USER(2)
    char username[NAMELEN]; //姓名
    char passwd[8];         //登录密码
    char recvmsg[DATALEN];  //通信的消息
    int flags;              //标志位
    staff_info_t info;      //员工信息
}MSG;

#define N 128

//数据库操作
#define DATABASE "my.db"

//
//登入管理员账号密码
// #define  admin  aitu
// #define  pwd    1234

sqlite3 *db;  //仅服务器使用

typedef struct{
    struct sockaddr_in addr;
    int acceptfd;
}MSG_t;

//添加历史的子函数
char hostroy_buf[DATALEN] = {0};
char *add_hosroy(MSG *msg,char *str)
{
    bzero(hostroy_buf,DATALEN);
    char sql[SQL_LEN] = {0};
    char *errmsg;
    sprintf(sql,"insert into historyinfo values('%s', '%s','%s')",Get_Time(),msg->username,str);
    printf("*************************\n");
    if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK){
		printf("插入失败：%s\n",errmsg);
		return NULL;
	}
    puts("插入成功");
    return hostroy_buf;
}

//--------------处理函数-------------//
int process_user_or_admin_login_request(int acceptfd,MSG *msg)
{
    printf("------------%s-----------%d.\n",__func__,__LINE__);
    //封装sql命令，表中查询用户名和密码－存在－登录成功－发送响应－失败－发送失败响应	
    printf("%s\n",msg->username);
    printf("%s\n",msg->passwd);
    printf("%#x\n",msg->msgtype);

    char sql[SQL_LEN] = {0};
    char **result;
    char *errmsg;
    int nrow,ncolumn;

    msg->info.usertype = msg->usertype;
    strcpy(msg->info.name,msg->username);
    strcpy(msg->info.passwd,msg->passwd);
    printf("usrtype: %#x-----usrname: %s---passwd: %s\n",\
    msg->info.usertype,msg->info.name,msg->info.passwd);
    
    sprintf(sql,"select * from usrinfo where usertype=%d and name='%s' and passwd='%s';",\
    msg->info.usertype,msg->info.name,msg->info.passwd);
    // sprintf(sql,"select * from usrinfo where name=%s ;",\
    // msg->info.name);
    if(sqlite3_get_table(db,sql,&result,&nrow,&ncolumn,&errmsg) != SQLITE_OK){
        printf("***********%s************* %d \n",errmsg,nrow);
    }else{
        if(nrow == 0){
            strcpy(msg->recvmsg,"name or passwd faild");
            send(acceptfd,msg,sizeof(MSG),0);
        }else{
            strcpy(msg->recvmsg,"OKOK");
            send(acceptfd,msg,sizeof(MSG),0);
        }
    }
    

    return 0;
}

//普通用户修改
int process_user_modify_request(int acceptfd,MSG *msg)
{
    printf("------------%s-----------%d.\n",__func__,__LINE__);
    char sql[SQL_LEN] = {0};
    char **result;
    char *errmsg;
    int nrow,ncolumn;
    //进行修改
    switch(msg->flags)
    {
        case 1://名字
            sprintf(sql,"update usrinfo set name='%s' where staffno=%d",msg->info.name,msg->info.no);
            break;
        case 2://密码
            sprintf(sql,"update usrinfo set passwd='%s' where staffno=%d",msg->info.passwd,msg->info.no);
            break;
        case 3://年龄
            sprintf(sql,"update usrinfo set age=%d where staffno=%d",msg->info.age,msg->info.no);
            break;
        case 4://电话
            sprintf(sql,"update usrinfo set phone='%s' where staffno=%d",msg->info.phone,msg->info.no);
            break;
        case 5://地址
            sprintf(sql,"update usrinfo set addr='%s' where staffno=%d",msg->info.addr,msg->info.no);
            break;
        case 6://职位
            sprintf(sql,"update usrinfo set work='%s' where staffno=%d",msg->info.work,msg->info.no);
            break;
        case 7://入职年月
            sprintf(sql,"update usrinfo set date='%s' where staffno=%d",msg->info.data,msg->info.no);
            break;
        case 8://等级
            sprintf(sql,"update usrinfo set level=%d where staffno=%d",msg->info.level,msg->info.no);
            break;
        case 9://薪水
            sprintf(sql,"update usrinfo set salary=%f where staffno=%d",msg->info.salary,msg->info.no);
            break;
        default:
            break;

    }
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK){
		printf("修改失败：%s\n",errmsg);
		return -1;
	}
    strcpy(msg->recvmsg,"YES");
    if(send(acceptfd,msg,sizeof(MSG),0) < 0){
        printf("发送失败\n");
        return -1;
    }
	puts("修改成功");

    return 0;
}

//普通用户查询
int process_user_query_request(int acceptfd,MSG *msg)
{
    printf("------------%s-----------%d.\n",__func__,__LINE__);
    //send(acceptfd, msg, sizeof(msg), 0);
    char sql[SQL_LEN] = {0};
    char **result;
    char *errmsg;
    int nrow,ncolumn;
    sprintf(sql,"select * from usrinfo where name='%s'",msg->recvmsg);
    if(sqlite3_get_table(db,sql,&result,&nrow,&ncolumn,&errmsg) != SQLITE_OK)
    {
        printf("errmsg:%s\n",errmsg);
        return -1;
    }
    else  //有bug，只会查到第一个人的信息。后期需要更新
    {   
        printf("run to %d \n",__LINE__);
        printf("msg->recvmsg = %s",msg->recvmsg);
        if(nrow != 0)
        {
            msg->info.no = atoi((result[11]));
            msg->info.usertype = atoi((result[12]));
            strcpy(msg->info.name,result[13]);
            strcpy(msg->info.passwd,result[14]);
            msg->info.age = atoi((result[15]));
            strcpy(msg->info.phone,result[16]);
            strcpy(msg->info.addr,result[17]);
            strcpy(msg->info.work,result[18]);
            strcpy(msg->info.data,result[19]);
            msg->info.level = atoi((result[20]));
            msg->info.salary = atof((result[21]));
            
            if (send(acceptfd,msg,sizeof(MSG),0) == -1)
            {
                printf("fail to send\n");
            }
            printf("到%d这里了\n",__LINE__);
        }else{
            strcpy(msg->recvmsg,"NOUSER");
            if(send(acceptfd,msg,sizeof(MSG),0) == -1)
            {
                printf("fail to send\n");
                return -1;
            }
            //printf("到%d这里了\n",__LINE__);
        }   
    }
    return 0;
}

//管理员修改
int process_admin_modify_request(int acceptfd,MSG *msg)
{
    printf("------------%s-----------%d.\n",__func__,__LINE__);
    char sql[SQL_LEN] = {0};
    char **result;
    char *errmsg;
    int nrow,ncolumn;
    //先判断用户是否存在
    sprintf(sql,"select * from usrinfo where staffno = %d",msg->info.no);
    if(sqlite3_get_table(db,sql,&result,&nrow,&ncolumn,&errmsg) != SQLITE_OK)
    {
        printf("errmsg:%s\n",errmsg);
        return -1;
    }
    printf("要修改的用户存在\n");
    strcpy(msg->recvmsg,"YES");
    if(send(acceptfd,msg,sizeof(MSG),0) < 0){
        printf("发送失败\n");
        return -1;
    }

    //进行修改
    switch(msg->flags)
    {
        case 1://名字
            sprintf(sql,"update usrinfo set name='%s' where staffno=%d",msg->info.name,msg->info.no);
            break;
        case 2://密码
            sprintf(sql,"update usrinfo set passwd='%s' where staffno=%d",msg->info.passwd,msg->info.no);
            break;
        case 3://年龄
            sprintf(sql,"update usrinfo set age=%d where staffno=%d",msg->info.age,msg->info.no);
            break;
        case 4://电话
            sprintf(sql,"update usrinfo set phone='%s' where staffno=%d",msg->info.phone,msg->info.no);
            break;
        case 5://地址
            sprintf(sql,"update usrinfo set addr='%s' where staffno=%d",msg->info.addr,msg->info.no);
            break;
        case 6://职位
            sprintf(sql,"update usrinfo set work='%s' where staffno=%d",msg->info.work,msg->info.no);
            break;
        case 7://入职年月
            sprintf(sql,"update usrinfo set date='%s' where staffno=%d",msg->info.data,msg->info.no);
            break;
        case 8://等级
            sprintf(sql,"update usrinfo set level=%d where staffno=%d",msg->info.level,msg->info.no);
            break;
        case 9://薪水
            sprintf(sql,"update usrinfo set salary=%f where staffno=%d",msg->info.salary,msg->info.no);
            break;
        default:
            break;

    }
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK){
		printf("修改失败：%s\n",errmsg);
		return -1;
	}
    strcpy(msg->recvmsg,"YES");
    if(send(acceptfd,msg,sizeof(MSG),0) < 0){
        printf("发送失败\n");
        return -1;
    }
	puts("修改成功");
    return 0;
}

//管理员添加
int process_admin_adduser_request(int acceptfd,MSG *msg)
{
    printf("------------%s-----------%d.\n",__func__,__LINE__);
    printf("run to %d",__LINE__);
    //填充sql语句
    char sql[SQL_LEN] = {0};
    char *errmsg;
	sprintf(sql,"insert into usrinfo values(%d,  %d, '%s', '%s', %d, '%s', '%s', '%s', '%s', %d, %f)",\
                msg->info.no,msg->info.usertype,msg->info.name,msg->info.passwd,msg->info.age,msg->info.phone,\
                msg->info.addr,msg->info.work,msg->info.data,msg->info.level,msg->info.salary);
    printf("*************************\n");
	//操作数据库插入
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK){
		printf("插入失败：%s\n",errmsg);
		return -2;
	}
	else{
        strcpy(msg->recvmsg,"ROK");
        if(send(acceptfd,msg,sizeof(MSG),0) < 0){
            printf("发送失败\n");
        }
		puts("插入成功");
	}

    printf("run to %d",__LINE__);
    return 0;

}

//管理员删除
int process_admin_deluser_request(int acceptfd,MSG *msg)
{
    printf("------------%s-----------%d.\n",__func__,__LINE__);
    char sql[SQL_LEN] = {0};
    char **result;
    char *errmsg;
    int nrow,ncolumn;
    //先判断用户是否存在
    sprintf(sql,"select * from usrinfo where staffno = %d",msg->info.no);
    if(sqlite3_get_table(db,sql,&result,&nrow,&ncolumn,&errmsg) != SQLITE_OK)
    {
        printf("errmsg:%s\n",errmsg);
        return -1;  
    }
    if(nrow == 0){
        printf("要删除的用户不存在\n");
        strcpy(msg->recvmsg,"ERR");
        if(send(acceptfd,msg,sizeof(MSG),0) < 0){
            printf("发送失败\n");
        }
        return -1;
    }
    printf("要删除的用户存在\n");

    //进行删除
    sprintf(sql,"delete from usrinfo where staffno=%d",msg->info.no);
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK){
		printf("删除失败：%s\n",errmsg);
		return -1;
	}
    strcpy(msg->recvmsg,"DEL");
    if(send(acceptfd,msg,sizeof(MSG),0) < 0){
        printf("发送失败\n");
        return -1;
    }
	puts("删除成功");
}

//管理员查询
int process_admin_query_request(int acceptfd,MSG *msg)
{
    printf("------------%s-----------%d.\n",__func__,__LINE__);
    char sql[SQL_LEN] = {0};
    char **result;
    char *errmsg;
    int nrow,ncolumn;
    int i = 11;
        if(msg->flags == 1) //按照人名查找
        {   
            printf("到%d这里了 flag = %d\n",__LINE__,msg->flags);
            sprintf(sql,"select * from usrinfo where name = '%s'",msg->info.name);
            if(sqlite3_get_table(db,sql,&result,&nrow,&ncolumn,&errmsg) != SQLITE_OK)
            {
                printf("errmsg:%s\n",errmsg);
            }
            // if (nrow == 0) {
            //     printf("该职员不存在!!!");
            // }
            else  //有bug，只会查到第一个人的信息。后期需要更新
            {
                msg->info.no = atoi((result[11]));
                msg->info.usertype = atoi((result[12]));
                strcpy(msg->info.name,result[13]);
                strcpy(msg->info.passwd,result[14]);
                msg->info.age = atoi((result[15]));
                strcpy(msg->info.phone,result[16]);
                strcpy(msg->info.addr,result[17]);
                strcpy(msg->info.work,result[18]);
                strcpy(msg->info.data,result[19]);
                msg->info.level = atoi((result[20]));
                msg->info.salary = atof((result[21]));
                
                if (send(acceptfd,msg,sizeof(MSG),0) == -1)
                {
                    printf("fail to send\n");
                }
                printf("到%d这里了\n",__LINE__);
            }
            printf("到%d这里了\n",__LINE__);
        }
        if(msg->flags == 2) //查找全部
        {
            
            sprintf(sql,"select * from usrinfo;");
            if(sqlite3_get_table(db,"select * from usrinfo",
                        &result,&nrow,&ncolumn,&errmsg
                        )!=SQLITE_OK){
                printf("查询失败：%s\n",errmsg);
                return -2;
            }
            else
            {
                printf("查询成功\n");
                if(nrow == 0){
                    strcpy(msg->recvmsg,"OK");
                    printf("怎么会执行到这里？\n");
                    send(acceptfd,msg,sizeof(MSG),0);
                    
                }else{
                    printf("查询成功 %d行   %d列\n ",nrow,ncolumn);
                    while(i < (ncolumn * (nrow + 1)))
                    {   
                        msg->info.no = atoi((result[i]));       i++;
                        msg->info.usertype = atoi((result[i])); i++;
                        strcpy(msg->info.name,result[i]);       i++;
                        strcpy(msg->info.passwd,result[i]);     i++;
                        msg->info.age = atoi((result[i]));      i++;
                        strcpy(msg->info.phone,result[i]);      i++;
                        strcpy(msg->info.addr,result[i]);       i++;
                        strcpy(msg->info.work,result[i]);       i++;
                        strcpy(msg->info.data,result[i]);       i++;
                        msg->info.level = atoi((result[i]));    i++;
                        msg->info.salary = atof((result[i]));   i++;
                        //printf("i = %d\n",i);
                        if (send(acceptfd,msg,sizeof(MSG),0) == -1)
                        {
                            printf("fail to send \n");
                        }
                        printf("发送成功 %d\n",__LINE__);
                    }
                    //printf("i = %d\n",i);
                    strcpy(msg->recvmsg,"END");
                    if(send(acceptfd,msg,sizeof(MSG),0) < 0){
                        printf("发送失败\n");
                        return -1;
                    }
                }
            } 
        }

    return 0;
}

//管理员查询历史
int process_admin_history_request(int acceptfd,MSG *msg)
{
    printf("------------%s-----------%d.\n",__func__,__LINE__);
    char sql[SQL_LEN] = {0};
    char **result;
    char *errmsg;
    int nrow,ncolumn;
    int i = 3;
    char buf[DATALEN] = {0};
    sprintf(sql,"select * from historyinfo;");
    if(sqlite3_get_table(db,sql,&result,&nrow,&ncolumn,&errmsg)!=SQLITE_OK){
        printf("查询失败：%s\n",errmsg);
        return -2;
    }
    else
    {
        printf("查询成功\n");
        if(nrow == 0){
            strcpy(msg->recvmsg,"NO");
            printf("怎么会执行到这里？\n");
            send(acceptfd,msg,sizeof(MSG),0);
            
        }else{
            printf("查询成功 %d行   %d列\n ",nrow,ncolumn);
            //(time text,name text,words text)
            while(i < (ncolumn * (nrow + 1)))
            {   
                sprintf(msg->recvmsg, "%s  %s   %s",result[i], result[i+1], result[i+2]);
                printf("拼接后的字符串：%s \n",msg->recvmsg);
                printf("i = %d\n",i);
                if (send(acceptfd,msg,sizeof(MSG),0) == -1)
                {
                    printf("fail to send \n");
                }
                printf("发送成功 %d\n",__LINE__);
                i = i + 3;
            }
            printf("i = %d\n",i);
            strcpy(msg->recvmsg,"HST");
            if(send(acceptfd,msg,sizeof(MSG),0) < 0){
                printf("发送失败\n");
                return -1;
            }
            printf("发送结束END成功\n");
        }
    }
    return 0;
}


int process_client_quit_request(int acceptfd,MSG *msg)
{
    printf("------------%s-----------%d.\n",__func__,__LINE__);
    printf("客户端即将退出 %d \n",msg->msgtype);
    return -1;
}

//选择请求类型
int process_client_request(int acceptfd,MSG *msg)
{
    printf("------------%s-----------%d.\n",__func__,__LINE__);
    switch (msg->msgtype)
    {
    case USER_LOGIN:
    case ADMIN_LOGIN:
        //printf("%s\n",msg->msgtype);
        process_user_or_admin_login_request(acceptfd,msg);
        add_hosroy(msg,"管理员登入");
        break;
    case USER_MODIFY:
        process_user_modify_request(acceptfd,msg);
        add_hosroy(msg,"普通用户修改");
        break;
    case USER_QUERY:
        process_user_query_request(acceptfd,msg);
        add_hosroy(msg,"普通用户修改查询");
        break;
    case ADMIN_MODIFY:
        process_admin_modify_request(acceptfd,msg);
        add_hosroy(msg,"管理员修改");
        break;
    case ADMIN_ADDUSER:
        process_admin_adduser_request(acceptfd,msg);
        add_hosroy(msg,"管理员添加");
        break;
    case ADMIN_DELUSER:
        process_admin_deluser_request(acceptfd,msg);
        add_hosroy(msg,"管理员删除");
        break;
    case ADMIN_QUERY:
        process_admin_query_request(acceptfd,msg);
        add_hosroy(msg,"管理员查询");
        break;
    case ADMIN_HISTORY:
        process_admin_history_request(acceptfd,msg);
        add_hosroy(msg,"管理员查询历史");
        break;
    case QUIT:
        process_client_quit_request(acceptfd,msg);
        add_hosroy(msg,"客户端退出");
        break;
    default:
        break;
    }
    //清除mag结构体
    bzero(msg,sizeof(MSG));
}

//多线程处理函数
void *pthread_fun(void *arg)
{
    printf("进入子线程了\n");
    ssize_t bytes;
    MSG_t msg1 = *(MSG_t *)arg;
    printf("[%s - %d]\n", inet_ntoa(msg1.addr.sin_addr),ntohs(msg1.addr.sin_port));


    MSG msg;
    int ret;
    while(1)
    {
        ret = recv(msg1.acceptfd,&msg,sizeof(MSG),0);
        printf("%d\n",ret);
        if(ret == 0){
            printf("接收到%d个字节\n",ret);
            close(msg1.acceptfd);
            break;
        }
        process_client_request(msg1.acceptfd,&msg);
        printf("********选择界面结束 %d*********\n",__LINE__);
        //bzero(&msg,sizeof(MSG));
    }
}


int main(int argc, const char * argv[])
{
    if(argc != 3){
        printf("Usage: <%s> <port> <ip> \n",argv[0]);
    }

    //----------------数据库------------------//
    //sqlite3 *db;   //此处重复定义了会造成数据库内有文件却查询不到
    //创建数据库
    if (sqlite3_open(DATABASE, &db) != SQLITE_OK)
    {
        printf("error : %s\n", sqlite3_errmsg(db));
        exit(-1);
    }
    //创建员工信息表
    char *errmsg;
    if(sqlite3_exec(db,"create table usrinfo(staffno integer  primary key,usertype integer, \
    name text,passwd text,age integer,phone text,addr text,work text,date text, \
    level integer,salary REAL);",NULL,NULL,&errmsg)!= SQLITE_OK)
    {
        printf("%s.\n",errmsg);
    }else{
        printf("create usrinfo table success.\n");
    }
    //创建历史记录表
    if(sqlite3_exec(db,"create table historyinfo(time text,name text,words text);",NULL,NULL,&errmsg)!= SQLITE_OK){
        printf("%s.\n",errmsg);
    }else{
        printf("create historyinfo table success.\n");
    }

    //---------------数据库（end）-------------//


    //创建套接子
    int socket_fd;
    socket_fd = socket(AF_INET, SOCK_STREAM, 0);
    if(socket_fd < 0){
        perror("socket()");
        return -1;
    }

    //将套接子设置为允许重复使用本机地址或者设置为端口复用
    int on = 1;
    if(setsockopt(socket_fd, SOL_SOCKET, SO_REUSEADDR, &on, sizeof(on)) < 0){
        printf("fail to setsockopt  \n");
        return -1;
    }

    //填充网络信息结构体
    struct sockaddr_in serveraddr,clientaddr;
    bzero(&serveraddr, sizeof(serveraddr));
    serveraddr.sin_family = AF_INET;
    serveraddr.sin_port   = htons(atoi(argv[1]));
    serveraddr.sin_addr.s_addr = inet_addr(argv[2]);


    //bind绑定
    int ret;
    ret = bind(socket_fd, (const struct sockaddr *)(&serveraddr), sizeof(serveraddr));
    if(ret < 0){
        perror("bind()");
        return -1;
    }

    //listen监听
    if(listen(socket_fd, 5) < 0){
        perror("listen");
        return -1;
    }

    //accept等待连接
    int connect_fd;
    socklen_t addrlen = sizeof(serveraddr);
    

    while(1)
    {
        connect_fd = accept(socket_fd, (struct sockaddr *)(&clientaddr), &addrlen );
        if(connect_fd < 0){
            perror("connect_fd()");
            return -1;
        }

        //客户端连接成功
        printf("connect OK \n");
        printf("%s - %d \n",inet_ntoa(clientaddr.sin_addr),ntohs(clientaddr.sin_port));

        MSG_t msg;
        pthread_t thread;
        msg.addr = clientaddr;
        msg.acceptfd = connect_fd;
        if(pthread_create(&thread, NULL, pthread_fun, &msg) != 0){
            printf("fail to pthread_create \n");
            return  -1;
        }

        //打印时间函数
        //printf("%s\n",Get_Time());

        pthread_detach(thread);
    }
    return 0;
}


